﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using WL.Model;

namespace WL.Dal
{
    /// <summary>
    /// 表格对象
    /// </summary>
    public class TableDal
    {
        private static string tb_filed = "wl_field";

        //读取所有表格对象
        public static DataTable getAllTable()
        {
            DataTable dt = SqliteMM.getMM().GetDataSet(CommandType.Text, "select id,name,sheetname from wl_table", null).Tables[0];
            return dt;
        }

        /// <summary>
        /// 根据id获取表格对象
        /// </summary>
        /// <param name="id">表格id</param>
        /// <returns></returns>
        public static Table getTableById(int id)
        {
            DataTable dt = SqliteMM.getMM().GetDataSet(CommandType.Text, "select * from wl_table where id=" + id, null).Tables[0];
            DataTable dtf = SqliteMM.getMM().GetDataSet(CommandType.Text, "select * from wl_field where pid=" + id, null).Tables[0];

            //表格对象初始化
            Table t = getTableByRow(dt.Rows[0]);
            //字段列表
            t.fields = getFieldsByDataTable(dtf, t);

            return t;
        }
        /// <summary>
        /// 根据id获取表格对象，不加载fields
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static Table getTableByIdLazy(int id)
        {
            DataTable dt = SqliteMM.getMM().GetDataSet(CommandType.Text, "select * from wl_table where id=" + id, null).Tables[0];
            Table t = getTableByRow(dt.Rows[0]);
            return t;
        }

        /// <summary>
        /// 根据DataTable对象获取表格字段列表
        /// </summary>
        /// <param name="dt">包含表格字段信息的dt对象</param>
        /// <param name="t">字段所属表格对象</param>
        /// <returns>字段列表</returns>
        private static List<Field> getFieldsByDataTable(DataTable dt, Table t)
        {
            List<Field> fields = new List<Field>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                fields.Add(getFieldByRow(dt.Rows[i], t));
            }

            return fields;
        }
        

        /// <summary>
        /// 从row获取表格字段对象
        /// </summary>
        /// <param name="row"></param>
        /// <param name="tb"></param>
        /// <returns></returns>
        private static Field getFieldByRow(DataRow row, Table tb = null)
        {
            Field f = new Field();
            f.pid = Convert.ToInt32(row["pid"]);
            f.id = Convert.ToInt32(row["id"]);
            f.name = row["name"].ToString();
            f.sortid = Convert.ToInt32(row["sortid"]);
            f.table = tb;
            f.typeid = Convert.ToInt32(row["type"]);
            return f;
        }
        /// <summary>
        /// 从row获取表格对象
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        private static Table getTableByRow(DataRow row)
        {
            Table t = new Table();
            t.ignore = Convert.ToInt32(row["ignore"]);
            t.id = Convert.ToInt32(row["id"]);
            t.name = (row["name"].ToString());
            t.sheetname = row["sheetname"].ToString();

            return t;
        }

        public static int createTable(string tbname)
        {
            string sql = "create table "+ tbname + "(id Integer primary key)";
            return SQLiteM.ExecuteNonQuery(CommandType.Text, sql);
        }

        public static int AddColumn(string colunmname, string table, string dbtype)
        {
            string sql = string.Format("ALTER  TABLE   {0}  ADD COLUMN  {1} {2} ", table, colunmname, dbtype);
            return SQLiteM.ExecuteNonQuery(CommandType.Text, sql);
        }

        public static object AddTable(string name, int ignore, string sheetname)
        {
            object id = SQLiteM.ExecuteInsert(CommandType.Text, "insert into wl_table values(NULL,'" + name + "'," + ignore +",'"+sheetname+"'" + ")");
            return id;
        }
        public static object AddField(string name, int type, int pid, int sortid, int search)
        {
            object id = SQLiteM.ExecuteInsert(CommandType.Text,
                                                  string.Format("insert into wl_field values(NULL,'{0}',{1},{2},{3},{4})", name, type, pid, sortid, search));
            return id;
        }

        internal static int updateField(int id, string name, int type, int pid, int sort, int search)
        {
            
            int ret = SQLiteM.ExecuteNonQuery(CommandType.Text,
                                                  string.Format("update wl_field set name='{0}',type={1},sortid={2},search={3} where id={4} ", name, type, sort, search, id));
            return ret;
        }

        public static int delTable(string id)
        {
            int ret = SQLiteM.ExecuteNonQuery(CommandType.Text,
                                    string.Format("delete from wl_table where id={0}", id));
            return ret;
        }

        public static int delField(int id)
        {
            int ret = SQLiteM.ExecuteNonQuery(CommandType.Text,
                                    string.Format("delete from wl_field where id={0}", id));
            return ret;
        }
        public static int delFields(string pid)
        {
            int ret = SQLiteM.ExecuteNonQuery(CommandType.Text,
                                    string.Format("delete from wl_field where pid={0}", pid));
            return ret;
        }


        internal static DataTable getAllFields(int id)
        {
            string where = " where pid="+id;

            string sql = "select * from " + tb_filed + where;
            return SQLiteM.GetDataSet(CommandType.Text, sql, null).Tables[0];
        }

        internal static Field getField(int id)
        {
            DataTable dt = SqliteMM.getMM().GetDataSet(CommandType.Text, "select * from wl_field where id=" + id, null).Tables[0];

            Field f= getFieldByRow(dt.Rows[0]);
            f.table = getTableByIdLazy(f.pid);

            return f;
            
        }




        internal static int updateTable(Table t)
        {
            int ret = SQLiteM.ExecuteNonQuery(CommandType.Text,
                                        string.Format("update wl_table set name='{0}',ignore={1},sheetname='{2}' where id={3}", t.name,t.ignore, t.sheetname, t.id));
            return ret;
        }

        /// <summary>
        /// 更改字段，此方法没有校验数据，如果更改字段的类型，可能导致错误
        /// </summary>
        /// <param name="old"></param>
        /// <param name="newf"></param>
        internal static void updateColumn(Field old, Field newf)
        {
            Random r = new Random();
            string tmpTable = old.table.name + "_tmp_" + r.Next(100);
            string sql = "ALTER TABLE " + "data_" + old.table.name + " RENAME TO '" + tmpTable + "'";
            SQLiteM.ExecuteNonQuery(CommandType.Text, sql); // 表改名

            
            //更改字段名
            //获取表的全部字段
            sql = "select * from wl_field where pid in(select id from wl_table where name='" + old.table.name + "') order by sortid";
            DataTable dt = SQLiteM.GetDataSet(CommandType.Text, sql).Tables[0];

            Table t = TableDal.getTableByName(old.table.name);
            List<Field> fields = getFieldsByDataTable(dt, t);
            sql = "create table data_" + old.table.name + "(line int,";
            string fieldNames = "line,";
            foreach (Field f in fields)
            {
                //fieldNames是用来获取原来数据（临时表)的字段
                if (f.name.Equals(newf.name))
                {
                    fieldNames += old.name  + ",";
                }
                else
                {
                    fieldNames += f.name + ",";
                }
                sql += f.name + " " + f.dbtype + ",";
            }
            sql = sql.Substring(0, sql.Length - 1);
            fieldNames = fieldNames.Substring(0, fieldNames.Length - 1);
            sql += ")";

            //sql = sql.Replace(old.name, newf.name);

            //创建新表
            SQLiteM.ExecuteNonQuery(CommandType.Text, sql);

            //将旧表数据导入新表
            //INSERT INTO "main"."data_l" SELECT "line" FROM "main"."oXHFcGcd04oXHFcGcd04_data_l"
            string sqldatas = "insert into data_" + old.table.name + " select " + fieldNames + " from " + tmpTable;
            SQLiteM.ExecuteNonQuery(CommandType.Text, sqldatas);

            //删除旧表
            string sql2 = "drop table " + tmpTable;
            SQLiteM.ExecuteNonQuery(CommandType.Text, sql2);


            
        }

        internal static int delColumn(string column, string table)
        {
            Random r  = new Random();
            string tmpTable = table + "_tmp_" + r.Next(100);

            string sql = "ALTER TABLE "+"data_"+table+" RENAME TO '"+tmpTable+"'";
            SQLiteM.ExecuteNonQuery(CommandType.Text, sql); // 表改名
            //int ret = 1;
          
            //获取表的全部字段
            sql = "select * from wl_field where pid in(select id from wl_table where name='"+table+"') and name !='"+column+"' order by sortid";
            DataTable dt = SQLiteM.GetDataSet(CommandType.Text, sql).Tables[0];

            Table t = TableDal.getTableByName(table);
                
            List<Field> fields = getFieldsByDataTable(dt, t);

            sql = "create table data_" + table + "(line int,";
            string fieldNames = "line,";
            foreach (Field f in fields)
            {
                fieldNames += f.name+",";
                sql += f.name+ " " + f.dbtype + ",";
            }   
            sql = sql.Substring(0,sql.Length-1);
            fieldNames = fieldNames.Substring(0, fieldNames.Length - 1);
            sql += ")";

            //创建新表
            SQLiteM.ExecuteNonQuery(CommandType.Text, sql);

            //将旧表数据导入新表
            //INSERT INTO "main"."data_l" SELECT "line" FROM "main"."oXHFcGcd04oXHFcGcd04_data_l"
            string sqldatas = "insert into data_"+table +" select "+fieldNames+" from "+tmpTable;
            SQLiteM.ExecuteNonQuery(CommandType.Text, sqldatas);

            //删除旧表
            string sql2 = "drop table "+tmpTable;
            SQLiteM.ExecuteNonQuery(CommandType.Text, sql2);

            
            
            return 1;
           

//CREATE TABLE "main"."tt" ("id" varchar,"line" int,"c" int,"rr" varchar,"sss" int DEFAULT (null) )
//INSERT INTO "main"."tt" SELECT "id","line","c","rr","ss" FROM "main"."oXHFcGcd04oXHFcGcd04_tt"
//DROP TABLE "main"."oXHFcGcd04oXHFcGcd04_tt"";
           // return true;
        }

        private static Table getTableByName(string table)
        {
            DataTable dt = SqliteMM.getMM().GetDataSet(CommandType.Text, "select * from wl_table where name='" + table+"'", null).Tables[0];
            //DataTable dtf = SqliteMM.getMM().GetDataSet(CommandType.Text, "select * from wl_field where pid=" + id, null).Tables[0];

            //表格对象初始化
            Table t = getTableByRow(dt.Rows[0]);
            //字段列表
            //t.fields = getFieldsByDataTable(dtf, t);

            return t;
        }

        /// <summary>
        /// 删除表
        /// </summary>
        /// <param name="tbname"></param>
        internal static void dropTable(string tbname)
        {
            string sql = "drop table "+ tbname;
            SQLiteM.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 更改表名
        /// </summary>
        /// <param name="oldname"></param>
        /// <param name="newname"></param>
        internal static void modifyTable(string oldname, string newname)
        {
            string sql = "ALTER TABLE " + oldname + " RENAME TO '" + newname+ "'";
            SQLiteM.ExecuteNonQuery(CommandType.Text, sql);
        }

        
    }

}